from osv import osv, fields
from osv.orm import intersect

class kdvn_project(osv.osv):
    _name = "kdvn.project"
    _description="KDVN Projects"
    
    def _update_history(self, cr, uid, ids,context):
        for kp in self.browse(cr,uid,ids):
            self.pool.get('kdvn.budget.history').create_history(cr,uid,kp.id)
        return True
    
    def _get_quotation_lists(self, cr, uid, ids, name, arg, context=None):
        res = {}
        for id in ids:
            cr.execute('SELECT id FROM sale_order ' \
                    'WHERE project_e_id =%s or project_m_id=%s' % (id,id))
            res[id] = [x[0] for x in cr.fetchall()]
        return res
    
    def _get_direct_cost(self, cr, uid, ids, name, arg, context=None):
        res = {}
        if ids:
            lst_tmp={'direct_amount_vnd':0.0,
                     'direct_amount_usd':0.0}
            for id in ids:
                res[id]=lst_tmp
            prj_id_list = ",".join(map(str,ids))
            cr.execute("Select \
                            kbd.project_id,\
                            sum(coalesce(budget_amount*received_rate,0)) as direct_cost_vnd,\
                            sum(case \
                            when rate =0\
                            then 0 \
                            else coalesce(budget_amount/rate,0) end) as direct_cost_usd\
                        from\
                            kdvn_budget_data kbd\
                        left join\
                            kdvn_project kp on kbd.project_id=kp.id\
                        left join\
                            account_budget_post abp on kbd.id=abp.id\
                        left join\
                            (Select parent_id,id,name as budget_cat from budget_category where parent_id>0) bc on categ_id=bc.id\
                        left join\
                            budget_category bc1 on bc.parent_id=bc1.id\
                        left join\
                            (Select\
                                id,\
                                case when sum(plan_cost_vnd+plan_vat_vnd) is null or sum(plan_cost_vnd+plan_vat_vnd)=0 then 1 else\
                                sum(contract_amount_vnd + contract_vat_vnd)/sum(plan_cost_vnd+plan_vat_vnd) end as received_rate\
                            from vwproject_contract_received_summary \
                            group by id) vwtemp on kp.contract_id = vwtemp.id\
                        left join\
                            project_currency pc on kp.contract_id=pc.contract_id and upper(pc.name)='USD'\
                        where bc1.cat_code='direct_cost' and kbd.project_id in (%s)\
                        group by kbd.project_id,bc1.cat_code" % prj_id_list)
            for prj_id,direct_vnd,direct_usd in cr.fetchall():
                res[prj_id]['direct_amount_vnd']=direct_vnd
                res[prj_id]['direct_amount_usd']=direct_usd
        return res


    def name_get(self, cr, uid, ids, context={}):
        if not len(ids):
            return []
        res = []
        reads = self.read(cr, uid, ids, ['name','code'], context)
        for record in reads:
            name=''
            if record:
                name = record['code']+" - "+record['name']
            res.append((record['id'], name))        
        return res
    
    def _get_project_cost_info(self, cr, uid, ids, name, args, context=None):
        res = {}
        if ids:
            project_info_lists={'project_amount_vnd':0.0,'project_vat_vnd':0.0,'project_total_vnd':0.0,
                                'project_amount_usd':0.0,'project_vat_usd':0.0,'project_total_usd':0.0,
                                'project_budget_total_vnd':0.0,'project_budget_total_usd':0.0}            
            for id in ids:
                res[id]=project_info_lists
            prj_id_list = ",".join(map(str, ids))
            
            cr.execute("Select \
                            kp.id,\
                            pc_usd.rate as exrate_usd,\
                            contract_total-contract_vat as contract_amount,\
                            contract_vat,\
                            contract_total,\
                            \
                            sum(budget_amount*received_rate) as project_budget_total_vnd,\
                            sum(case when coalesce(pc_usd.rate,0)=0 then 0 else budget_amount/pc_usd.rate end) as project_budget_total_usd,\
                            \
                            (case when coalesce(pc_vnd.rate,0)=0 then 0 else project_amount*project_exrate_reference/pc_vnd.rate end)*received_rate as project_amount_vnd,\
                            (case when coalesce(pc_vnd.rate,0)=0 then 0 else project_vat*project_exrate_reference/pc_vnd.rate end)*received_rate as project_vat_vnd,\
                            (case when coalesce(pc_vnd.rate,0)=0 then 0 else project_total*project_exrate_reference/pc_vnd.rate end)*received_rate as project_total_vnd,\
                            \
                            (case when coalesce(pc_usd.rate,0)=0 then 0 else project_amount*project_exrate_reference/pc_usd.rate end) as project_amount_usd,\
                            (case when coalesce(pc_usd.rate,0)=0 then 0 else project_vat*project_exrate_reference/pc_usd.rate end) as project_vat_usd,\
                            (case when coalesce(pc_usd.rate,0)=0 then 0 else project_total*project_exrate_reference/pc_usd.rate end) as project_total_usd\
                        from\
                            kdvn_project kp\
                        left join \
                            kdvn_contract_client kcc on kp.contract_id=kcc.id\
                        left join\
                            project_currency pc_usd on kcc.id=pc_usd.contract_id and pc_usd.name='USD'\
                        left join\
                            project_currency pc_vnd on kcc.id=pc_vnd.contract_id and pc_vnd.name='VND'\
                        left join\
                            kdvn_budget_data kbd on kp.id = kbd.project_id\
                        left join\
                            (Select\
                                id,\
                                case when sum(plan_cost_vnd+plan_vat_vnd) is null or sum(plan_cost_vnd+plan_vat_vnd)=0 then 1 else\
                                sum(contract_amount_vnd + contract_vat_vnd)/sum(plan_cost_vnd+plan_vat_vnd) end as received_rate\
                            from vwproject_contract_received_summary \
                            group by id) vwtemp on kp.contract_id = vwtemp.id\
                            where kp.id in (%s)\
                        group by kp.id,received_rate,pc_vnd.rate,\
                                project_amount,project_vat,project_total,\
                                project_exrate_reference,pc_usd.rate,\
                                contract_total,contract_vat" % (prj_id_list))            
        for prj_list in cr.dictfetchall():
            tmp_id=prj_list.pop('id')
            res[tmp_id].update(prj_list)
        return res
    
    _columns ={
                'code':fields.char('Project Code',size=30,required=1,select=1),
                'name':fields.char('Project Name',size=30,required=1,select=1),
                
                'registration_date':fields.date("Reg. Date",select=1),
                'started_date':fields.date('Started Date',select=1),
                'completion_date':fields.date('Completion Date',select=1),
                'closed_date':fields.date('Closed Date',select=1),
                                
                'project_type':fields.selection([('E','Electrical'),('M','Mechanical')],"Project Type",select=1,required=True),
                'project_status':fields.selection([('doing','Doing'),('done','Done'),('cancel','Canceled')],"Prj. Status"),
                
                'description':fields.text("Description"),
                
                'project_manager_id':fields.many2one("res.users",'Prj. Manager'),
                'site_manager_id':fields.many2one('res.users','Site Manager'),
                'contract_id':fields.many2one('kdvn.contract.client','Contract',select=1),
                
                'budget_data_ids':fields.one2many('kdvn.budget.data','project_id','Budget Lines'),
                'budget_history_ids':fields.one2many('kdvn.budget.history','project_id','Budget History'),
                
                'quotation_lists':fields.function(_get_quotation_lists,relation='sale.order',type='one2many',string='Quotations List',method=True),
                
                'owner_id':fields.many2one('res.partner','Owner',readonly=True),
                'client_id':fields.many2one('res.partner','Client',readonly=True),
                'address_id':fields.many2one('res.partner.address','Address',readonly=True),
                'invoice_address_id':fields.many2one('res.partner.address','Invoice Address',readonly=True),
                                
                'project_currency_reference':fields.many2one('project.currency','Cur.',readonly=True),
                'project_exrate_reference':fields.float('Ex.Rate',readonly=True),
                'project_amount':fields.float('Amount',readonly=True),
                'project_vat':fields.float('V.A.T.',readonly=True),
                'project_total':fields.float('Total',readonly=True),
                
                #Project Cost Info VND & USD
                    #Contract Info
                'exrate_usd':fields.function(_get_project_cost_info,string='Amount in VND',type='float',method=True,multi='project_cost_info'),
                'contract_amount':fields.function(_get_project_cost_info,string='Amount in VND',type='float',method=True,multi='project_cost_info'),
                'contract_vat':fields.function(_get_project_cost_info,string='Amount in VND',type='float',method=True,multi='project_cost_info'),
                'contract_total':fields.function(_get_project_cost_info,string='Amount in VND',type='float',method=True,multi='project_cost_info'),
                
                'project_amount_vnd':fields.function(_get_project_cost_info,string='Amount in VND',type='float',method=True,multi='project_cost_info'),
                'project_vat_vnd':fields.function(_get_project_cost_info,string='VAT in VND',type='float',method=True,multi='project_cost_info'),
                'project_total_vnd':fields.function(_get_project_cost_info,string='Total in VND',type='float',method=True,multi='project_cost_info'),
                
                'project_amount_usd':fields.function(_get_project_cost_info,string='Amount in USD',type='float',method=True,multi='project_cost_info'),
                'project_vat_usd':fields.function(_get_project_cost_info,string='VAT in USD',type='float',method=True,multi='project_cost_info'),
                'project_total_usd':fields.function(_get_project_cost_info,string='Total in USD',type='float',method=True,multi='project_cost_info'),
                
                'project_budget_total_vnd':fields.function(_get_project_cost_info,string='Project Budget in VND',type='float',method=True,multi='project_cost_info'),
                'project_budget_total_usd':fields.function(_get_project_cost_info,string='Project Budget in USD',type='float',method=True,multi='project_cost_info'),
                
                'direct_amount_vnd':fields.function(_get_direct_cost,string='Direct In VND',type='float',method=True,multi='direct_cost'),
                'direct_amount_usd':fields.function(_get_direct_cost,string='Direct In USD',type='float',method=True,multi='direct_cost'),
                }
    _sql_constraints = [('code_unique_project', 'unique (code)', 'KDVN Error: The code of the project must be unique !')] 
    _defaults = {  
        'project_type':lambda *x:"E",
        'project_status':lambda *x:"doing",
        'project_vat':lambda *x:0.0,
        'project_total':lambda *x:0.0,
        'project_exrate_reference':lambda *x:0.0,
        }    
kdvn_project()